package modele.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import db.MySQLSingleton;

import modele.beanEntity.Comment;
import modele.beanEntity.Recipe;
import modele.beanEntity.User;

public class CommentMapper {
	
	private Connection db;
	
	public CommentMapper() throws Exception{
		this.db=MySQLSingleton.getInstance();
	}
	
	public Comment getComment(int id) throws Exception{
		RecipeMapper rm = new RecipeMapper();
		UserMapper um = new UserMapper();
		
		PreparedStatement ps =this.db.prepareStatement("SELECT * FROM comments WHERE idcomments= ?");
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			Comment c = new Comment(rs.getInt("idcomments"),rs.getString("txt"),rs.getInt("rating"),
					um.getUser(rs.getInt("idusers")),rm.getRecipe(rs.getInt("idreceipes")));
			rs.close();
			ps.close();
			return c;
		}
		
		rs.close();
		ps.close();
		
		return null;
	}
	
	public List<Comment> getRecipeCommentList(int recipeId) throws Exception{
		RecipeMapper rm = new RecipeMapper();
		UserMapper um = new UserMapper();
		PreparedStatement ps=this.db.prepareStatement("SELECT * FROM comments WHERE idreceipes = ?");
		ps.setInt(1, recipeId);
		ResultSet rs= ps.executeQuery();
		List<Comment> l=new ArrayList<Comment>();
		while(rs.next()){
			l.add(new Comment(rs.getInt("idcomments"),rs.getString("txt"),rs.getInt("rating"),
					um.getUser(rs.getInt("idusers")),rm.getRecipe(rs.getInt("idreceipes"))));
		}
		rs.close();
		ps.close();
		return l;
	}

	public boolean createComment(String txt,int rating,int userId,int recipeId) throws SQLException{
		PreparedStatement ps = this.db.prepareStatement("INSERT INTO comments(txt,rating,idusers,idreceipes) VALUES (?,?,?,?);");
		ps.setString(1, txt);
		ps.setInt(2, rating);
		ps.setInt(3, userId);
		ps.setInt(4, recipeId);
		
		if(ps.executeUpdate()== 0){
			ps.close();
			return false;
		}
		ps.close();
		return true;
	}
	
	public boolean createComment(Comment c) throws SQLException{
		return this.createComment(c.getTxt(), c.getRating(), c.getUser().getId(), c.getRecipe().getId());
	}
	
	public boolean createComment(String txt,int rating,User u,Recipe r) throws SQLException{
		return this.createComment(txt, rating, u.getId(), r.getId());
	}
	
	public boolean updateComment(int id,String txt,int rating,int userId,int recipeId) throws SQLException{
		PreparedStatement ps = this.db.prepareStatement("UPDATE comments SET txt=?,rating=?,idusers=?,idreceipes=? WHERE idcomments=?;");
		ps.setString(1, txt);
		ps.setInt(2, rating);
		ps.setInt(3, userId);
		ps.setInt(4, recipeId);
		ps.setInt(5, id);
		if(ps.executeUpdate()== 0){
			ps.close();
			return false;
		}
		ps.close();
		return true;
	}
	
	public boolean updateComment(Comment c) throws SQLException{
		return this.updateComment(c.getId(),c.getTxt(),c.getRating(),c.getUser().getId(),c.getRecipe().getId());
	}
	
	public boolean deleteComment(int id) throws SQLException{
		PreparedStatement ps = this.db.prepareStatement("DELETE FROM comments WHERE idcomments= ?");
		ps.setInt(1,id);
		if(ps.executeUpdate() == 0){
			ps.close();
			return false;
		}
		ps.close();
		return true;
	}
	
	public boolean deleteComment(Comment c) throws SQLException{
		return this.deleteComment(c.getId());
	}
}
